package cadastro;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import connection.ConnectionOracleOR;

public class Empresa {
	
	
	public void save( int NID_EMPRESA, 
				String VCNPJ, 
				String VRAZAO_SOCIAL,
				String VEMAIL, 
				String VSITE,
				String VRUA,
				int    NNUMERO, 
				String VBAIRRO, 
				String VCIDADE, 
				String VESTADO,
				int    NDDDTELEFONE1,
				String VTELEFONE1, 
				int    NDDDTELEFONE2,
				String VTELEFONE2) throws Exception{
	
		try{
		
			ConnectionOracleOR oracleBruno 	= 	new ConnectionOracleOR( );
			Connection conexao 				= 	oracleBruno.newConnection( );			
			
			if( NID_EMPRESA == 0 ){
				
				CallableStatement cs 			= 	conexao.prepareCall( "{call INSERIR_EMPRESA(?,?,?,?,?,?,?,?,?,?,?,?,?)}" ) ;  
				cs.setString(1, VCNPJ);
				cs.setString(2, VRAZAO_SOCIAL);
				cs.setString(3, VEMAIL);
				cs.setString(4, VSITE);
				cs.setString(5, VRUA);
				cs.setInt(6, NNUMERO );
				cs.setString(7, VBAIRRO);
				cs.setString(8, VCIDADE);
				cs.setString(9, VESTADO);
				cs.setInt(10, NDDDTELEFONE1 );
				cs.setString(11, VTELEFONE1);
				cs.setInt(12, NDDDTELEFONE2 );
				cs.setString(13, VTELEFONE2);
				
				cs.executeQuery();
				
			}else{
				
				CallableStatement cs 			= 	conexao.prepareCall( "{call EDITAR_EMPRESA(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}" ) ;  
				cs.setInt(1, NID_EMPRESA );
				cs.setString(2, VCNPJ);
				cs.setString(3, VRAZAO_SOCIAL);
				cs.setString(4, VEMAIL);
				cs.setString(5, VSITE);
				cs.setString(6, VRUA);
				cs.setInt(7, NNUMERO );
				cs.setString(8, VBAIRRO);
				cs.setString(9, VCIDADE);
				cs.setString(10, VESTADO);
				
				System.out.println(NDDDTELEFONE1);
				System.out.println(VTELEFONE1);
				System.out.println(NDDDTELEFONE2);
				System.out.println(VTELEFONE2);
				
				cs.setInt(11, NDDDTELEFONE1 );
				cs.setString(12, VTELEFONE1);
				cs.setInt(13, NDDDTELEFONE2 );
				cs.setString(14, VTELEFONE2);
				
				cs.executeQuery();
			}

			
			     
		
		}catch( Exception e){
		
			throw new Exception("erro: "+e.getMessage());
		}
	
	
	}	
	
	
	
	public void remover(int NID_EMPRESA) throws Exception{
		
		try{
			
			ConnectionOracleOR oracleBruno 	= 	new ConnectionOracleOR( );
			Connection conexao 				= 	oracleBruno.newConnection( );			
			
			
			CallableStatement cs 			= 	conexao.prepareCall( "{call REMOVER_EMPRESA(?)}" ) ;  
			cs.setInt(1, NID_EMPRESA );
			
			cs.executeQuery();
	        
		}catch( Exception e){
			
			throw new Exception("erro: "+e.getMessage());
		}

	}

	
	public ResultSet load( int NID_EMPRESA ) throws Exception{
		
		ResultSet result=null;
		
		
		try{
			
			ConnectionOracleOR oracleBruno 	= 	new ConnectionOracleOR( );
			Connection conexao 				= 	oracleBruno.newConnection( );			
			
			Statement statement 			= 	conexao.createStatement();
	        ResultSet rs 					= 	null;	        
	        String 	query					=	"select NID_EMPRESA,  VCNPJ,  VRAZAO_SOCIAL,  VEMAIL,  VSITE,  VRUA,  NNUMERO,  VBAIRRO,  VCIDADE,  VESTADO,  DDD1 NDDDTELEFONE1,  TELEFONE1 VTELEFONE1 ,  DDD2 NDDDTELEFONE2,  TELEFONE2 VTELEFONE2 from V_DADOS_EMPRESA where NID_EMPRESA="+NID_EMPRESA;
            
	        rs = statement.executeQuery( query );
	        
	        //if(rs.next()){	        		       
	        result = rs;		        
	        //}

	        //rs.close();
	        //statement.close();                
	        //conexao.close();
	        
		}catch( Exception e){
			
			throw new Exception("erro: "+e.getMessage());
		}


		return result;
	}

	

	
	private String createGrid(String body){
		
		String result="";
		
		result += "<table style='width:100%;' border='0' class='grid'>" +
					"<thead>" +
						"<tr>" +
						"<td style='width:140px;'>&nbsp;</td>" +
							"<td>NID_EMPRESA</td>" +
							"<td>VCNPJ</td>" +
							"<td>VRAZAO_SOCIAL</td>" +
							"<td>VEMAIL</td>" +
							"<td>VSITE</td>" +							
							"<td>VRUA</td>" +
							"<td>NNUMERO</td>" +
							"<td>VBAIRRO</td>" +
							"<td>VCIDADE</td>" +
							"<td>VESTADO</td>" +
						"</tr>" +
					"</thead>" +
					"<tbody>"+
						body+
					"</tbody>" +
					"<tfoot>" +
						"<tr>" +
							"<td colspan='12'> </td>" +
						"</tr>" +
					"</tfoot>" +
				"</table>";
						
		
		return result;
	}


	public String grid() throws Exception{
		
		String result="";
		
		try{
			
			ConnectionOracleOR oracleBruno 	= 	new ConnectionOracleOR( );
			Connection conexao 				= 	oracleBruno.newConnection( );			
			
			Statement statement 			= 	conexao.createStatement();
	        ResultSet rs 					= 	null;	        
	        String 	query					=	"select NID_EMPRESA,  VCNPJ,  VRAZAO_SOCIAL,  VEMAIL,  VSITE,  VRUA,  NNUMERO,  VBAIRRO,  VCIDADE,  VESTADO  from V_DADOS_EMPRESA";
            
	        rs = statement.executeQuery( query );
	        int i=1;
	        
	        		       
	        while (rs.next()){
	        	
	        	if(i==1)
	        		i++;
	        	else
	        		i=1;
	        	
	        	result += "<tr class='linha"+i+"'>" +
	        			"<td><a class='image_icon' href='editar/?action=editar&NID_EMPRESA="+rs.getInt("NID_EMPRESA")+"' title='Editar'><img width='32px' src='../../images/icon/icon_edit.png'></a> <a class='image_icon' href='editar/?action=remover&NID_EMPRESA="+rs.getInt("NID_EMPRESA")+"' title='Excluir'><img width='32px' src='../../images/icon/icon_delete.png'></a> <a class='image_icon' href='../departamento/?NID_EMPRESA="+rs.getInt("NID_EMPRESA")+"' title='Departamento'><img width='32px' src='../../images/icon/icon_departamento.png'></a></td>" +
	    				"<td>"+rs.getInt("NID_EMPRESA")+"</td>" +
	    				"<td>"+rs.getString("VCNPJ")+"</td>" +
	    				"<td>"+rs.getString("VRAZAO_SOCIAL")+"</td>" +
	    				"<td>"+rs.getString("VEMAIL")+"</td>" +
	    				"<td>"+rs.getString("VSITE")+"</td>" +
	    				"<td>"+rs.getString("VRUA")+"</td>" +
	    				"<td>"+rs.getInt("NNUMERO")+"</td>" +
	    				"<td>"+rs.getString("VBAIRRO")+"</td>" +
	    				"<td>"+rs.getString("VCIDADE")+"</td>" +
	    				"<td>"+rs.getString("VESTADO")+"</td>" +
	    				"</tr>";     
	        	
	        }
		        
	        if( result.equals("") ){
	        	
	        	result = "<tr class='linha1' align='center'>" +
	    				"<td colspan='12'>sem registros</td>" +
	    				"</tr>";     
	        	
	        }
	        
	        rs.close();
	        statement.close();                
	        conexao.close();
	        
		}catch( Exception e){
			
			throw new Exception("erro: "+e.getMessage());
		}


		return this.createGrid( result );
	}
	
	
	public ArrayList<String> viewAll( int StartLimit, int EndLimit) throws Exception{
		
		ArrayList<String> result = new ArrayList<String>();
		
		try{
			
			ConnectionOracleOR oracleBruno 	= 	new ConnectionOracleOR( );
			Connection conexao 				= 	oracleBruno.newConnection( );			
			
			Statement statement 			= 	conexao.createStatement();
	        ResultSet rs 					= 	null;	        
	        String 	query					=	"select * from TP_USUARIO " +
	        		"							where VNOME='' ";
            
	        rs = statement.executeQuery( query );
	        
	        if (rs.next()){
	        	
	        	//if(rs.getInt("existe")>0)
	        	//	result=true;     
	        	
	        }
	        	    
	        
	        rs.close();
	        statement.close();                
                
		}catch( Exception e){
			
			throw new Exception("erro: "+e.getMessage());
		}


		return result;
	}

}
